﻿using System;
using System.Data;
using System.Configuration;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Globalization;
using System.Data.SqlClient;
namespace FrameWork.DataBaseProvider
{
    #region 委托
    /// <summary>
    /// 数据库操作命令委托
    /// </summary>
    /// <param name="command">操作命令</param>
    /// <returns>委托的命令</returns>
    public delegate object CommandDelegate(DbCommand dbcommand);
    /// <summary>
    /// DbDataReader命令委托
    /// </summary>
    /// <param name="dbDataReader">DbDataReader</param>
    public delegate void DBDataReaderDelegate(DbDataReader dbDataReader);
    #endregion

    /// <summary>
    ///数据库操作的辅助类
    /// </summary>
    public class DbHelper
    {
        #region 字段
        private static DbHelper instance;
        private ConnectionStringSettings connectionStringSettings = null;
        private DbProviderFactory dbProviderFactory = null;
        #endregion

        #region 构造函数
        public DbHelper()
        {
            connectionStringSettings = ConfigurationManager.ConnectionStrings[FrameWork.DataBaseProvider.ConnectString.DataConnenctString];
            if (connectionStringSettings != null)
            {
                if (!string.IsNullOrEmpty(connectionStringSettings.ProviderName) && !string.IsNullOrEmpty(connectionStringSettings.ConnectionString))
                    dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
            }
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="DbConnectSettingName">连接字符串名称</param>
        public DbHelper(string dbConnectSettingName)
        {
            if (string.IsNullOrEmpty(dbConnectSettingName))
            {
                dbConnectSettingName = FrameWork.DataBaseProvider.ConnectString.DataConnenctString;
            }
            connectionStringSettings = ConfigurationManager.ConnectionStrings[dbConnectSettingName];
            if (connectionStringSettings != null)
            {
                if (!string.IsNullOrEmpty(connectionStringSettings.ProviderName) && !string.IsNullOrEmpty(connectionStringSettings.ConnectionString))
                    dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
            }
        }
        #endregion

        #region 类属性
        public static DbHelper Instance
        {
            get
            {
                if (instance == null)
                    instance = new DbHelper("");
                return instance;
            }
        }
        #endregion
        #region 类实例属性
        public string ConnectString
        {
            get { return connectionStringSettings.ConnectionString; }
        }
        public DbProviderFactory ProviderFactory
        {
            get { return dbProviderFactory; }
        }
        public string ProviderString
        {
            get { return connectionStringSettings.ProviderName; }
        }
        #endregion

        /// <summary>
        /// 创建一个数据库连接
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateConnection()
        {
            if (dbProviderFactory == null)
            {
                return null;
            }
            else
            {
                DbConnection dbConnection = dbProviderFactory.CreateConnection();
                dbConnection.ConnectionString = connectionStringSettings.ConnectionString;
                return dbConnection;
            }
        }

        public DbDataReader ExecuteReader(string sql)
        {
            DbConnection dbCnn = CreateConnection();
            DbCommand cmd = dbProviderFactory.CreateCommand();

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Connection = dbCnn;
            dbCnn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);

        }
        /// <summary>
        /// 执行无结果集Sql
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteNonQuery(CommandType commandType, string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                try
                {
                    return cmd.ExecuteNonQuery();
                }
                catch
                {
                    return -1;
                }
            };
            return (int)ExecuteCmdCallback(commandType, sql, cd);
        }

        /// <summary>
        /// 执行带参数的非查询语句
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="para">参数集合</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteNonQuery(CommandType commandType, string sql, DbParameter[] para)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteNonQuery();
            };
            return (int)ExecuteCmdCallback(commandType, sql, cd, para);
        }
        /// <summary>
        ///  执行带参数与委托命令的查询语句，并返回相关的委托命令
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="commandDelegate">委托类型</param>
        /// <param name="para">参数集合</param>
        /// <returns>委托的命令</returns>
        private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate commandDelegate, DbParameter[] para)
        {
            using (DbConnection dbCnn = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.CommandType = commandType;
                    cmd.CommandText = sql;
                    cmd.Connection = dbCnn;
                    for (int i = 0; i < para.GetLength(0); i++)
                    {
                        cmd.Parameters.Add(para[i]);
                    }
                    dbCnn.Open();
                    return commandDelegate(cmd);
                }
            }
        }

        /// <summary>
        /// 通过DbDataReader来读取数据
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="readdelegate">DBDataReaderDelegate</param>
        public bool ReadData(string sql, DBDataReaderDelegate readdelegate)
        {
            bool result = false;
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataReader dbReader = cmd.ExecuteReader())
                {
                    readdelegate(dbReader);
                    return true;
                }
            };
            result = (bool)ExecuteCmdCallback(CommandType.Text, sql, cd);
            return result;
        }

        private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate commandDelegate)
        {
            using (DbConnection dbCon = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.CommandType = commandType;
                    cmd.CommandText = sql;
                    cmd.Connection = dbCon;
                    dbCon.Open();
                    return commandDelegate(cmd);
                }
            }
        }

        private object ExecuteCmdCallback(CommandType commandType, CommandDelegate commandDelegate)
        {
            using (DbConnection dbCon = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection = dbCon;
                    cmd.CommandType = commandType;
                    dbCon.Open();
                    return commandDelegate(cmd);
                }
            }
        }

        /// <summary>
        /// 根据Sql创建一个DataTable结果集
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable CreateDataTable(CommandType commandType, string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Locale = CultureInfo.InvariantCulture;
                    dt.Load(dr);
                    return dt;
                }
            };
            return (DataTable)ExecuteCmdCallback(CommandType.Text, sql, cd);
        }
     
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns>结果集中第一行的第一列</returns>
        public string GetValue(string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteScalar();
            };
            object value = ExecuteCmdCallback(CommandType.Text, sql, cd);
            if (value == null)
                return "";
            return value.ToString();
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns>结果集中第一行的第一列</returns>
        public Object GetObject(string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteScalar();
            };
            return ExecuteCmdCallback(CommandType.Text, sql, cd);
        }

        /// <summary>
        /// 执行Sql取数，并绑定到BaseDataList 类控件如 DataList 和 DataGrid。
        /// </summary>
        /// <param name="dl">待绑定控件</param>
        /// <param name="sql">取数sql</param>
        public void Bind(BaseDataList dl, string sql)
        {
            ReadData(sql, delegate(DbDataReader dr)
            {
                dl.DataSource = dr;
                dl.DataBind();
            });
        }
        /// <summary>
        /// 绑定DropDownList
        /// </summary>
        /// <param name="Ddl">DropDownList</param>
        /// <param name="Sql">Sql语句</param>
        /// <param name="TextField">为DropDownList文本指定数据源</param>
        /// <param name="ValueField">为DropDownList的值指定数据源</param>
        public void Bind(DropDownList dropDownList, string sql, string textField, string valueField)
        {
            ReadData(sql, delegate(DbDataReader dr)
            {
                dropDownList.DataTextField = textField;
                dropDownList.DataValueField = valueField;
                dropDownList.DataSource = dr;
                dropDownList.DataBind();
            });
        }
        /// <summary>
        /// 绑定ListBox
        /// </summary>
        /// <param name="listBox">ListBox</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="textField">为ListBox文本指定数据源</param>
        /// <param name="valueField">为ListBox的值指定数据源</param>
        public void Bind(ListBox listBox, string sql, string textField, string valueField)
        {
            ReadData(sql, delegate(DbDataReader dr)
            {
                listBox.DataTextField = textField;
                listBox.DataValueField = valueField;
                listBox.DataSource = dr;
                listBox.DataBind();
            });
        }
        /// <summary>
        /// 根据Sql语句创建一个DataSet类型的结果集
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <returns>相关数据集</returns>
        public DataSet CreateDataSet(CommandType commandType, string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataAdapter da = dbProviderFactory.CreateDataAdapter())
                {

                    DataSet ds = new DataSet();
                    ds.Locale = CultureInfo.InvariantCulture;
                    da.SelectCommand = cmd;
                    da.Fill(ds);
                    return ds;
                }
            };
            return (DataSet)ExecuteCmdCallback(CommandType.Text, sql, cd);
        }

        /// <summary>
        /// 根据SQL返回分页数据集
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="currPageIndex">当前页</param>
        /// <param name="dataSetName">数据集名称</param>
        /// <returns>返回分页后的数据集</returns>
        public DataSet GetPagerDataSet(CommandType commandType, string sql, int pageSize, int currPageIndex, string dataSetName)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataAdapter da = dbProviderFactory.CreateDataAdapter())
                {

                    DataSet ds = new DataSet();
                    ds.Locale = CultureInfo.InvariantCulture;
                    da.SelectCommand = cmd;
                    da.Fill(ds, pageSize * (currPageIndex - 1), pageSize, dataSetName);
                    return ds;
                }
            };
            return (DataSet)ExecuteCmdCallback(CommandType.Text, sql, cd);
        }
        /// <summary>
        /// 执行Sql取数据，并绑定到 GridView、DetailsView 和 TreeView控件
        /// </summary>
        /// <param name="baseDataBoundControl">待绑定控件</param>
        /// <param name="sql">取数sql</param>
        public void Bind(BaseDataBoundControl baseDataBoundControl, string sql)
        {
            ExecuteCmdCallback(CommandType.Text, sql, delegate(DbCommand cmd)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    baseDataBoundControl.DataSource = dr;
                    baseDataBoundControl.DataBind();
                }
                return null;
            });
        }


        /// <summary>
        /// 执行多条SQL语句，中间用分号‘;’隔开。
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public bool ExeMoreDB(string strSQL)
        {
            char[] de = { ';' };
            string strChildSQL;
            int i;
            string[] strSQLArr = strSQL.Split(de);
            bool IsControll = true;

            DbConnection conn = CreateConnection();
            conn.Open();
            DbCommand myCommand = conn.CreateCommand();
            DbTransaction myTrans;
            myTrans = conn.BeginTransaction();
            myCommand.Transaction = myTrans;
            try
            {
                for (i = 0; i < strSQLArr.Length; i++)
                {
                    strChildSQL = strSQLArr[i];
                    myCommand.CommandText = strChildSQL;
                    myCommand.ExecuteNonQuery();
                }
                myTrans.Commit();
                IsControll = true;
            }
            catch (Exception)
            {
                try
                {
                    IsControll = false;
                    myTrans.Rollback();
                }
                catch
                {
                }
            }
            finally
            {
                conn.Close();
            }
            return IsControll;
        }
    }
}

